package rent_car.Utils;

import rent_car.domain.CarBase;

import java.sql.*;
import java.util.ArrayList;

public class Utils {

    static Connection conn;
    static{
        try {
            Class.forName("com.mysql.jdbc.Driver");
            try {
               conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/machen",
                                                            "root", "root");
            } catch (SQLException e) {
                System.out.println("数据库连接失败！");
            }
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败！");
        }
    }
    public static Connection getConnection(){
        return conn;
    }

    //插入操作
    public static void insert(CarBase car){
        Connection conn=getConnection();

        String sql="insert into carBase values(?,?,?,?,?)";
        PreparedStatement ps=null;
        try {
            ps= conn.prepareStatement(sql);
            ps.setInt(1,car.getId());
            ps.setString(2,car.getName());
            ps.setString(3,car.getcSize());
            ps.setString(4,car.getpSize());
            ps.setDouble(5,car.getPrice());
            ps.executeUpdate();
        } catch (SQLException e) {
           System.out.println("sql语句加载失败！");
        }finally {
            try {
               if(ps!=null) ps.close();    //如果ps执行失败，此时的ps为空，为避免空指针异常
                if(conn!=null) conn.close();;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //查询某个数据表是否为空
    public static boolean isEmpty(String tableName){
        Connection conn=getConnection();
        String sql="select * from "+tableName;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=conn.prepareStatement(sql);
            rs= ps.executeQuery();
            if(!rs.next()){ //如果结果集的下一个是空的，那么结果集是空的
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("查询语句执行失败！");
        }finally {
                try {

                    if(rs!=null) rs.close();
                    if(ps!=null) ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return false;
    }

    //根据车的类型查询车的信息
    public static ArrayList<CarBase> findByCarType(int type){
        Connection conn=getConnection();
        //多表查询
        String sql=" select * from carbase left join cartype on carbase.type=cartype.name where carbase.type=?";
        PreparedStatement ps=null;
        ResultSet rs=null;
        ArrayList<CarBase> arrayList=new ArrayList<CarBase>();
        try {
            ps= conn.prepareStatement(sql);
            ps.setInt(1,type);
            rs=ps.executeQuery();
            while(rs.next()){
                CarBase car = new CarBase();
                car.setId(rs.getInt("id"));
                car.setName(rs.getString("name"));
                car.setcSize(rs.getString("cSize"));
                car.setpSize(rs.getString("pSize"));
                car.setPrice(rs.getDouble("price"));
                car.setType(rs.getInt("carbase.type"));
                arrayList.add(car); //将对象添加到ArrayList中
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("查询车辆信息失败！");
        }finally {
            try {
                if(ps!=null) ps.close();    //如果ps执行失败，此时的ps为空，为避免空指针异常

                if(rs!=null) rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return arrayList;
    }
    //通过id查找车辆
    public static CarBase findById(int id){
        CarBase car = new CarBase();
        Connection conn=getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;

        String sql="select * from carbase where id=?";
        try {
             ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();
              while(rs.next()){
                  car.setPrice(rs.getDouble("price"));
                  car.setType(rs.getInt("type"));
                  car.setcSize(rs.getString("cSize"));
                  car.setpSize(rs.getString("pSize"));
                  car.setName(rs.getString("name"));
                  return car;
              }

        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("通过id查找车辆失败！");
        }finally {
            try {
                if(ps != null) ps.close();;
                if(rs != null) rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return null;
    }

    public static void main(String[] args){
        CarBase carBase = new CarBase();
        carBase=findById(2);
        System.out.println(carBase.getName());

    }
    
    
}
